عملگر PIVOT
عملگر PIVOT داده‌ها را در رديف‌هاي مجزا دريافت کرده و با هم جمع مي‌کند، سپس نتيجه را در ستون‌هاي مجاور هم نمايش مي‌دهد. براي ديدن عملکرد دقيق عملگر PIVOT، بايد ابتدا با استفاده از کدفهرست 1 يک جدول ايجاد کرده و چند رکورد را در آن درج کنيد.
CREATE TABLE pivot_test (
id NUMBER,
customer_id NUMBER,
product_code VARCHAR2(5),
quantity NUMBER
);
INSERT INTO pivot_test VALUES (1, 1, ‹A›, 10);
INSERT INTO pivot_test VALUES (2, 1, ‹B›, 20);
INSERT INTO pivot_test VALUES (3, 1, ‹C›, 30);
INSERT INTO pivot_test VALUES (4, 2, ‹A›, 40);
INSERT INTO pivot_test VALUES (5, 2, ‹C›, 50);
INSERT INTO pivot_test VALUES (6, 3, ‹A›, 60);
INSERT INTO pivot_test VALUES (7, 3, ‹B›, 70);
INSERT INTO pivot_test VALUES (8, 3, ‹C›, 80);
INSERT INTO pivot_test VALUES (9, 3, ‹D›, 90);
INSERT INTO pivot_test VALUES (10, 4, ‹A›, 100);
COMMIT;
فهرست 1
با اجراي کد فوق ده رکورد در جدول ذخيره مي‌شود و مي‌توان با استفاده از کد فهرست 2 داده‌هاي ذخيره شده در جدول را مشاهده كرد.
SELECT * FROM pivot_test;
ID CUSTOMER_ID PRODU QUANTITY
---------- ----------- ----- ----------
1 1 A 10
2 1 B 20
3 1 C 30
4 2 A 40
5 2 C 50
6 3 A 60
7 3 B 70
8 3 C 80
9 3 D 90
10 4 A 100
10 rows selected.
SQL>
فهرست 2
عملگر PIVOT در حالت پايه، عملکرد محدودي دارد. ما مجبور هستيم تا مقاديري را که مي‌خواهيم چرخانده شده و از رديف‌هاي ستون مورد نظر به نام ستون‌هاي ما تبديل شوند، دقيقاً مشخص کنيم. به همين دليل، بايد دقيقاً مقاديري از ستون مورد نظر را که قرار است از ستون به نام سطر تبديل شوند، مطابق با کد فهرست 3 و استفاده از عبارت IN مشخص کنيم.
SELECT *
FROM (SELECT product_code, quantity
FROM pivot_test)
PIVOT (SUM(quantity) AS sum_quantity FOR (product_code) IN (‹A› AS a, ‹B› AS b, ‹C› AS c));
A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY
-------------- -------------- --------------
210 90 160
1 row selected.
SQL>
فهرست 3
همان‌طور که مشاهده مي‌شود، در خروجي پرس‌وجوي فوق مقادير مشخص شده از ستون کد محصول، يعني رکوردهاي با مقدار A و B و C به ستون‌هاي جدول تبديل شده‌اند و جمع محصول فروخته شده به ازاي هر يک از اين محصولات در يک سطر از خروجي پرس‌وجو قرار مي‌گيرد. اگر مي‌خواهيد اين نتيجه را خرد کرده و جمع فروش هر محصول را به ازاي هر مشتري داشته‌باشيد، کافي است تا به سادگي نام ستون CUSTOMER_ID را به پارامتر‌هاي عملگر SELECT اضافه کنيد. کد حاصل و خروجي آن به شکل فهرست 4 خواهد بود.
SELECT *
FROM (SELECT customer_id, product_code, quantity
FROM pivot_test)
PIVOT (SUM(quantity) AS sum_quantity FOR (product_code) IN (‹A› AS a, ‹B› AS b, ‹C› AS c))
ORDER BY customer_id;
CUSTOMER_ID A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY
----------- -------------- -------------- --------------
1 10 20 30
2 40 50
3 60 70 80
4 100
4 rows selected.
SQL>
فهرست4
افزودن کلمه کليدي XML به عملگر PIVOT به ما امکان مي‌دهد تا نتايج چرخانده‌شده حاصل از عملگر PIVOT را نه به صورت خروجي استاندارد عملگر SQL، بلکه به فرمت و ساختار XML در اختيار داشته‌باشيم. اين کار همچنين عملگر PIVOT را انعطاف‌پذير‌تر مي‌کند. به اين ترتيب، در اين حالت ديگر مجبور نيستيد مقادير مشخصي از ستون مورد نظر را به صورت Hard-code در قسمت IN مشخص کنيد، بلکه مي‌توانيد در برابر اين عملگر از يک زيرپرس‌وجو(sub Query) يا عملگر ANY استفاده کنيد، دو نمونه كد براي نمايش نحوه تركيب عملگر PIVOT با XML در سايت مجله قرار داده شده است. مطابق با حالت معمولي PIVOT در حالت XML نيز مي‌توان نتايج پرس‌وجو را به ازاي هر مشتري تفکيک کرد، به اين ترتيب، به ازاي هر کد مشتري، يک فايل XML مجزا، شامل تعداد فروخته شده از هر محصول به آن مشتري توليد مي‌شود. كدهاي مورد نياز براي ايجاد يك فايل XML مجزا براي هر مشتري نيز در سايت مجله آورده شده است.
عملگر UNPIVOT
عملگر UNPIVOT بر خلاف عملگر قبلي بوده و داده‌هاي ستوني را در سطرهاي مجزا ذخيره مي‌کند، براي مشاهده نتيجه عملگر UNPIVOT با استفاده از کد فهرست 5 يک جدول ايجاد کرده و 4 رکورد در آن درج کنيد.
CREATE TABLE unpivot_test (
id NUMBER,
customer_id NUMBER,
product_code_a NUMBER,
product_code_b NUMBER,
product_code_c NUMBER,
product_code_d NUMBER
);
INSERT INTO unpivot_test VALUES (1, 101, 10, 20, 30, NULL);
INSERT INTO unpivot_test VALUES (2, 102, 40, NULL, 50, NULL);
INSERT INTO unpivot_test VALUES (3, 103, 60, 70, 80, 90);
INSERT INTO unpivot_test VALUES (4, 104, 100, NULL, NULL, NULL);
COMMIT;
فهرست 5
داده‌هاي جدول فهرست 5 فوق مطابق با خروجي پرس‌وجوي فهرست 6 قابل مشاهده است.
SELECT * FROM unpivot_test;
ID CUSTOMER_ID PRODUCT_CODE_A PRODUCT_CODE_B PRODUCT_CODE_C PRODUCT_CODE_D
---------- ----------- -------------- -------------- -------------- --------------
1 101 10 20 30
2 102 40 50
3 103 60 70 80 90
4 104 100
4 rows selected.
SQL>
فهرست 6
عملگر UNPIVOT داده‌هايي را که به صورت ستوني ذخيره‌شده‌اند، در رديف‌هاي مجزا، نمايش مي‌دهد همانند فهرست 7.
SELECT *
FROM unpivot_test
UNPIVOT (quantity FOR product_code IN (product_code_a AS ‹A›, product_code_b AS ‹B›, product_code_c AS ‹C›, product_code_d AS ‹D›));
ID CUSTOMER_ID P QUANTITY
---------- ----------- - ----------
1 101 A 10
1 101 B 20
1 101 C 30
2 102 A 40
2 102 C 50
3 103 A 60
3 103 B 70
3 103 C 80
3 103 D 90
4 104 A 100
10 rows selected.
SQL>
فهرست 7
هنگام استفاده از اين عبارت، بايد نكته‌هاي زير را در نظر داشت:
نام‌هاي مورد نياز براي ستون‌ها که در اين مورد عبارت‌هاي QUANTITY و PRODUCT_CODE است، در قسمت مرتبط با عبارت UNPIVOT تعريف شده‌اند. اين عبارت‌ها را مي‌توان با هر نامي که در حال حاضر در جدول مورد پردازش وجود نداشته‌باشد، جايگزين کرد.
SELECT *
FROM unpivot_test
UNPIVOT INCLUDE NULLS (quantity FOR product_code IN (product_code_a AS ‹A›, product_code_b AS ‹B›, product_code_c AS ‹C›, product_code_d AS ‹D›));
ID CUSTOMER_ID P QUANTITY
---------- ----------- - ----------
1 101 A 10
1 101 B 20
1 101 C 30
1 101 D
2 102 A 40
2 102 B
2 102 C 50
2 102 D
3 103 A 60
3 103 B 70
3 103 C 80
3 103 D 90
4 104 A 100
4 104 B
4 104 C
4 104 D
16 rows selected.
SQL>
فهرست 8
ستون‌هايي را که مي‌خواهيم از حالت ستوني به سطر تبديل شوند بايد در قسمت عبارت IN قرار دهيم. مقادير رکوردها در ستون PRODUCT_CODE با نام ستون يا ستون‌هايي که به رکورد تبديل شده‌اند، متناسب خواهد بود مگر آن‌که براي آن‌ها از نام مستعار استفاده کنيد. به صورت پيش‌فرض اين کد شامل عملگر EXCLUDE NULLS است. مي‌توانيد با استفاده از عملگر INCLUDE NULLS مقادير NULL را نيز به رکوردهايي با مقدار NULL به ازاي ستون مورد نظر، تبديل کنيد.در كد فهرست 8 نحوه استفاده از INCLUDE NULLS را در يك پرس‌وجو مشاهده مي‌كنيد.
نظرات شما عزیزان:
تبادل لینک هوشمند 
خبرنامه وب سایت:
آمار
وب سایت:
